﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text; 
using System.Data.SqlClient; 
using System.Data;
using System.Transactions;
using Stone.DataContracts;
using Stone.Library.DB;

namespace CourseArraging.DAO
{
    public class XyDAO
    {

        public void Add(XyEntity xyEntity)
        {
            TransactionOptions option = new TransactionOptions();
            option.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, option))
            {
                try
                {
                    StringBuilder builder = new StringBuilder();
                    builder.Append("insert into qdxy(");
                    builder.Append("qdxy_xybh,qdxy_jybh,qdxy_kcsj,qdxy_skdd,qdxy_fzmks,qdxy_ymks,qdxy_jsfs,qdxy_ksrq,qdxy_jsrq,qdxy_bz,qdxy_zwh,qdxy_zks,qdxy_km,qdxy_zt)");
                    builder.Append(" values (");
                    builder.Append("@qdxy_xybh,@qdxy_jybh,@qdxy_kcsj,@qdxy_skdd,@qdxy_fzmks,@qdxy_ymks,@qdxy_jsfs,@qdxy_ksrq,@qdxy_jsrq,@qdxy_bz,@qdxy_zwh,@qdxy_zks,@qdxy_km,'正在授课')");
                    builder.Append(";select @@IDENTITY");
                    SqlParameter[] sqlParams = new SqlParameter[] { 
                        new SqlParameter("@qdxy_xybh", SqlDbType.VarChar, 50), 
                        new SqlParameter("@qdxy_jybh", SqlDbType.VarChar, 50), 
                        new SqlParameter("@qdxy_kcsj", SqlDbType.VarChar, 300), 
                        new SqlParameter("@qdxy_skdd", SqlDbType.VarChar, 300), 
                        new SqlParameter("@qdxy_fzmks", SqlDbType.VarChar, 50), 
                        new SqlParameter("@qdxy_ymks", SqlDbType.VarChar, 50), 
                        new SqlParameter("@qdxy_jsfs", SqlDbType.VarChar, 50), 
                        new SqlParameter("@qdxy_ksrq", SqlDbType.VarChar, 50), 
                        new SqlParameter("@qdxy_jsrq", SqlDbType.VarChar, 50),
                        new SqlParameter("@qdxy_bz", SqlDbType.VarChar, 500), 
                        new SqlParameter("@qdxy_zwh", SqlDbType.VarChar, 50), 
                        new SqlParameter("@qdxy_zks", SqlDbType.VarChar, 50), 
                        new SqlParameter("@qdxy_km", SqlDbType.VarChar, 50) };
                    sqlParams[0].Value = xyEntity.qdxy_xybh;
                    sqlParams[1].Value = xyEntity.qdxy_jybh;
                    sqlParams[2].Value = xyEntity.qdxy_kcsj;
                    sqlParams[3].Value = xyEntity.qdxy_skdd;
                    sqlParams[4].Value = xyEntity.qdxy_fzmks;
                    sqlParams[5].Value = xyEntity.qdxy_ymks;
                    sqlParams[6].Value = xyEntity.qdxy_jsfs;
                    sqlParams[7].Value = xyEntity.qdxy_ksrq;
                    sqlParams[8].Value = xyEntity.qdxy_jsrq;
                    sqlParams[9].Value = xyEntity.qdxy_bz;
                    sqlParams[10].Value = xyEntity.qdxy_zwh;
                    sqlParams[11].Value = xyEntity.qdxy_zks;
                    sqlParams[12].Value = xyEntity.qdxy_km;
                    int id=DBExecuteUtil.executeSqlWithPar(builder.ToString(), sqlParams);


                    string s1 = "update stu_info set stu_info_arranged='已安排' where stu_info_no='" + xyEntity.qdxy_xybh + "'";
                    DBExecuteUtil.executeSql(s1);
                    string s2 = "update tec_info set tec_info_busy_string=tec_info_busy_string+'" + xyEntity.qdxy_kcsj +
                        "',tec_info_time_busy=tec_info_time_busy+'" + xyEntity.qdxy_busy_simple +
                        "' where tec_info_no='"+xyEntity.qdxy_jybh+"'";
                    DBExecuteUtil.executeSql(s2);
                    string[] tmp=xyEntity.qdxy_kcsj.Split(new char[2]{'[',']'},StringSplitOptions.RemoveEmptyEntries);
                    LsapDAO lsapdao = new LsapDAO();
                    for (int i = 0; i < tmp.Length;i++ )
                    {
                        lsapdao.Add(new LsapEntity()
                        {
                            lsap_jybh = xyEntity.qdxy_jybh,
                            lsap_kcsj = tmp[i],
                            lsap_km = xyEntity.qdxy_km,
                            lsap_xybh = xyEntity.qdxy_xybh,
                            lsap_zt = xyEntity.qdxy_zt,
                            lsap_bz = xyEntity.qdxy_bz
                        });
                    }
                    scope.Complete();
                }
                finally
                {
                    scope.Dispose();
                }
            }
        }

        public void Delete(int qdxy_id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from qdxy ");
            builder.Append(" where qdxy_id=@qdxy_id ");
            SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter("@qdxy_id", SqlDbType.Int, 4) };
            sqlParams[0].Value = qdxy_id;
            DBExecuteUtil.executeSqlWithPar(builder.ToString(), sqlParams);
        }

        public XyEntity GetEntity(int qdxy_id)
        {
            XyEntity entity = new XyEntity();
            StringBuilder builder = new StringBuilder();
            builder.Append("select qdxy_id,qdxy_no,qdxy_xybh,qdxy_jybh,qdxy_kcsj,qdxy_skdd,qdxy_fzmks,qdxy_ymks,qdxy_jsfs,qdxy_ksrq,qdxy_jsrq,qdxy_bz,qdxy_zwh,qdxy_zks,qdxy_km,qdxy_zt, ");
            builder.Append(" stu_info_name, stu_info_lxr, stu_info_gddh, stu_info_phone,stu_info_dep,stu_info_address,");
            builder.Append("tec_info_name,tec_info_grade,tec_info_course,tec_info_dep,tec_info_gddh,tec_info_phone,qdxy_sjqk ");
            builder.Append(" FROM qdxy,stu_info,tec_info  ");
            builder.Append(" where qdxy_id=@qdxy_id and qdxy.qdxy_xybh = stu_info_no AND qdxy.qdxy_jybh = tec_info_no");
            SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@qdxy_id", SqlDbType.Int, 4) };
            sqlParas[0].Value = qdxy_id;
            DataTable table = DBExecuteUtil.querySqlTable(builder.ToString(), sqlParas);
            if (table.Rows.Count > 0)
            {
                if (table.Rows[0]["qdxy_id"].ToString() != "")
                {
                    qdxy_id = int.Parse(table.Rows[0]["qdxy_id"].ToString());
                }
                entity.qdxy_no = table.Rows[0]["qdxy_no"].ToString();
                entity.qdxy_xybh = table.Rows[0]["qdxy_xybh"].ToString();
                entity.qdxy_jybh = table.Rows[0]["qdxy_jybh"].ToString();
                entity.qdxy_kcsj = table.Rows[0]["qdxy_kcsj"].ToString();
                entity.qdxy_skdd = table.Rows[0]["qdxy_skdd"].ToString();
                entity.qdxy_fzmks = table.Rows[0]["qdxy_fzmks"].ToString();
                entity.qdxy_ymks = table.Rows[0]["qdxy_ymks"].ToString();
                entity.qdxy_jsfs = table.Rows[0]["qdxy_jsfs"].ToString();
                entity.qdxy_ksrq = table.Rows[0]["qdxy_ksrq"].ToString();
                entity.qdxy_jsrq = table.Rows[0]["qdxy_jsrq"].ToString();
                entity.qdxy_bz = table.Rows[0]["qdxy_bz"].ToString();
                entity.qdxy_zwh = table.Rows[0]["qdxy_zwh"].ToString();
                entity.qdxy_zks = table.Rows[0]["qdxy_zks"].ToString();
                entity.qdxy_km = table.Rows[0]["qdxy_km"].ToString();
                entity.qdxy_zt = table.Rows[0]["qdxy_zt"].ToString();
                entity.qdxy_stu_name = table.Rows[0]["stu_info_name"].ToString();
                entity.qdxy_stu_lxr = table.Rows[0]["stu_info_lxr"].ToString();
                entity.qdxy_stu_lxdh = (table.Rows[0]["stu_info_phone"].ToString() == "0") ? table.Rows[0]["stu_info_gddh"].ToString() : table.Rows[0]["stu_info_phone"].ToString();
                entity.qdxy_stu_dep = table.Rows[0]["stu_info_dep"].ToString();
                entity.qdxy_stu_address = table.Rows[0]["stu_info_address"].ToString();
                entity.qdxy_tec_name = table.Rows[0]["tec_info_name"].ToString();
                entity.qdxy_tec_grade = table.Rows[0]["tec_info_grade"].ToString();
                entity.qdxy_tec_course = table.Rows[0]["tec_info_course"].ToString();
                entity.qdxy_tec_dep = table.Rows[0]["tec_info_dep"].ToString();
                entity.qdxy_tec_lxdh = (table.Rows[0]["tec_info_phone"].ToString() == "0") ? table.Rows[0]["tec_info_gddh"].ToString() : table.Rows[0]["tec_info_phone"].ToString();
                entity.qdxy_sjqk = table.Rows[0]["qdxy_sjqk"].ToString();
            }
            return entity;
        }

        public DataTable GetList(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select qdxy_id 协议流水,qdxy_no 协议编号,qdxy_xybh 学员编号,stu_info_name 学员,")
                .Append("qdxy_jybh 教员编号,tec_info_name 教员,qdxy_zks 总课时,qdxy_km 科目,")
                .Append("qdxy_skdd 上课地点,qdxy_zwh 座位号,qdxy_kcsj 课程时间,qdxy_fzmks '分钟/课时',qdxy_ymks '元/课时', ")
                .Append("qdxy_ksrq 开始日期,qdxy_jsrq 结束日期,qdxy_jsfs 结算方式,qdxy_bz 备注,qdxy_sjqk 授课情况,qdxy_zt 授课状态 ");
            builder.Append(" FROM qdxy,tec_info,stu_info where qdxy.qdxy_xybh=stu_info.stu_info_no and qdxy.qdxy_jybh=tec_info_no ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                builder.Append(" and " + strWhere);
            }
            return DBExecuteUtil.querySqlTable(builder.ToString());
        }

        public void Update(XyEntity xyEntity)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update qdxy set ");
            builder.Append("qdxy_xybh=@qdxy_xybh,");
            builder.Append("qdxy_jybh=@qdxy_jybh,");
            builder.Append("qdxy_kcsj=@qdxy_kcsj,");
            builder.Append("qdxy_skdd=@qdxy_skdd,");
            builder.Append("qdxy_fzmks=@qdxy_fzmks,");
            builder.Append("qdxy_ymks=@qdxy_ymks,");
            builder.Append("qdxy_jsfs=@qdxy_jsfs,");
            builder.Append("qdxy_ksrq=@qdxy_ksrq,");
            builder.Append("qdxy_jsrq=@qdxy_jsrq,");
            builder.Append("qdxy_bz=@qdxy_bz,");
            builder.Append("qdxy_zwh=@qdxy_zwh,");
            builder.Append("qdxy_zks=@qdxy_zks,");
            builder.Append("qdxy_km=@qdxy_km,");
            builder.Append("qdxy_zt=@qdxy_zt,");
            builder.Append("qdxy_sjqk=@qdxy_sjqk ");
            builder.Append(" where qdxy_id=@qdxy_id ");
            SqlParameter[] sqlParams = new SqlParameter[] { 
                new SqlParameter("@qdxy_id", SqlDbType.Int, 4), 
                new SqlParameter("@qdxy_xybh", SqlDbType.VarChar, 50), 
                new SqlParameter("@qdxy_jybh", SqlDbType.VarChar, 50), 
                new SqlParameter("@qdxy_kcsj", SqlDbType.VarChar, 300), 
                new SqlParameter("@qdxy_skdd", SqlDbType.VarChar, 300), 
                new SqlParameter("@qdxy_fzmks", SqlDbType.VarChar, 50), 
                new SqlParameter("@qdxy_ymks", SqlDbType.VarChar, 50),
                new SqlParameter("@qdxy_jsfs", SqlDbType.VarChar, 50), 
                new SqlParameter("@qdxy_ksrq", SqlDbType.VarChar, 50), 
                new SqlParameter("@qdxy_jsrq", SqlDbType.VarChar, 50), 
                new SqlParameter("@qdxy_bz", SqlDbType.VarChar, 500), 
                new SqlParameter("@qdxy_zwh", SqlDbType.VarChar, 50),
                new SqlParameter("@qdxy_zks", SqlDbType.VarChar, 50), 
                new SqlParameter("@qdxy_km", SqlDbType.VarChar, 50),
                new SqlParameter("@qdxy_zt", SqlDbType.VarChar, 50),
                new SqlParameter("@qdxy_sjqk",SqlDbType.VarChar,1000)};
            sqlParams[0].Value = xyEntity.qdxy_id;
            sqlParams[1].Value = xyEntity.qdxy_xybh;
            sqlParams[2].Value = xyEntity.qdxy_jybh;
            sqlParams[3].Value = xyEntity.qdxy_kcsj;
            sqlParams[4].Value = xyEntity.qdxy_skdd;
            sqlParams[5].Value = xyEntity.qdxy_fzmks;
            sqlParams[6].Value = xyEntity.qdxy_ymks;
            sqlParams[7].Value = xyEntity.qdxy_jsfs;
            sqlParams[8].Value = xyEntity.qdxy_ksrq;
            sqlParams[9].Value = xyEntity.qdxy_jsrq;
            sqlParams[10].Value = xyEntity.qdxy_bz;
            sqlParams[11].Value = xyEntity.qdxy_zwh;
            sqlParams[12].Value = xyEntity.qdxy_zks;
            sqlParams[13].Value = xyEntity.qdxy_km;
            sqlParams[14].Value = xyEntity.qdxy_zt;
            sqlParams[15].Value = xyEntity.qdxy_sjqk;
            DBExecuteUtil.executeSqlWithPar(builder.ToString(), sqlParams);
        }

        public void UpdateState(string qdxy_id, string qdxy_zt)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update qdxy set ");
            builder.Append("qdxy_zt=@qdxy_zt");
            builder.Append(" where qdxy_id=@qdxy_id ");
            SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter("@qdxy_id", SqlDbType.Int, 4), new SqlParameter("@qdxy_zt", SqlDbType.VarChar, 50) };
            sqlParams[0].Value = qdxy_id;
            sqlParams[1].Value = qdxy_zt;
            DBExecuteUtil.executeSqlWithPar(builder.ToString(), sqlParams);
        }
    }
}
